import { NextRequest, NextResponse } from "next/server";
import { SqliteDatabase } from "@/lib/sqlite-database";
import fs from 'fs';
import path from 'path';

export async function GET(request: NextRequest) {
  try {
    const database = new SqliteDatabase();

    // 获取所有设备和它们的基础参数表ID
    const allDevices = database['db']?.prepare('SELECT id, device_id, name, specialty, base_params_table_id FROM devices').all() as any[] || [];

    // 统计基础参数表引用情况
    const tableUsage = new Map<string, Array<{device_id: number, name: string, specialty: string}>>();
    const unmappedDevices = [];

    for (const device of allDevices) {
      const tableId = device.base_params_table_id;
      if (!tableId || tableId.trim() === '' || tableId === '-') {
        unmappedDevices.push(device);
        continue;
      }

      if (!tableUsage.has(tableId)) {
        tableUsage.set(tableId, []);
      }
      tableUsage.get(tableId)!.push({
        device_id: device.device_id,
        name: device.name,
        specialty: device.specialty
      });
    }

    // 读取Excel基础参数表并解析可用的表
    const csvFilePath = path.join(process.cwd(), 'excel', '06-21', '属性表-基础参数表.csv');
    let availableTables = new Map<string, {description: string, parameterCount: number}>();

    if (fs.existsSync(csvFilePath)) {
      const csvContent = fs.readFileSync(csvFilePath, 'utf-8');
      const lines = csvContent.split('\n').filter(line => line.trim() !== '');

      let currentTableId = '';
      let currentDescription = '';
      let parameterCount = 0;

      for (const line of lines) {
        const columns = line.split(',');
        if (columns.length >= 6) {
          const sequence = columns[0]?.trim() || '';
          const name = columns[1]?.trim() || '';

          // 检查是否是表标识行
          if (sequence.includes('表 C.') || sequence.includes('表C.')) {
            // 保存前一个表的信息
            if (currentTableId && parameterCount > 0) {
              availableTables.set(currentTableId, {
                description: currentDescription,
                parameterCount: parameterCount
              });
            }

            // 开始新表
            const tableMatch = sequence.match(/表\s*C\.[\d-]+/);
            if (tableMatch) {
              currentTableId = tableMatch[0].replace(/\s+/g, '');
              currentDescription = name || '';
              parameterCount = 0;
            }
            continue;
          }

          // 跳过标题行
          if (sequence === '序号' && name === '中文名称') {
            continue;
          }

          // 计算参数数量
          if (name && name !== '' && currentTableId && sequence.match(/^\d+$/)) {
            parameterCount++;
          }
        }
      }

      // 保存最后一个表的信息
      if (currentTableId && parameterCount > 0) {
        availableTables.set(currentTableId, {
          description: currentDescription,
          parameterCount: parameterCount
        });
      }
    }

    // 分析匹配情况
    const matchAnalysis = [];
    const unmatchedTables = [];

    for (const [tableId, devices] of tableUsage) {
      const exactMatch = availableTables.has(tableId);
      let fuzzyMatch = null;

      if (!exactMatch) {
        // 尝试模糊匹配
        const normalizedTableId = tableId.replace(/\s+/g, '').replace('表', '').replace('C', '').replace('.', '');
        for (const [excelTableId] of availableTables) {
          const normalizedExcelTableId = excelTableId.replace(/\s+/g, '').replace('表', '').replace('C', '').replace('.', '');
          if (
            normalizedExcelTableId === normalizedTableId ||
            normalizedExcelTableId.includes(normalizedTableId) ||
            normalizedTableId.includes(normalizedExcelTableId)
          ) {
            fuzzyMatch = excelTableId;
            break;
          }
        }
      }

      if (exactMatch || fuzzyMatch) {
        const matchedTable = exactMatch ? tableId : fuzzyMatch;
        matchAnalysis.push({
          deviceTableId: tableId,
          matchedExcelTable: matchedTable,
          matchType: exactMatch ? 'exact' : 'fuzzy',
          deviceCount: devices.length,
          parameterCount: availableTables.get(matchedTable!)?.parameterCount || 0,
          devices: devices
        });
      } else {
        unmatchedTables.push({
          deviceTableId: tableId,
          deviceCount: devices.length,
          devices: devices
        });
      }
    }

    // 获取当前数据库中的基础参数统计
    const currentBaseParams = database['db']?.prepare(`
      SELECT COUNT(*) as total_params, COUNT(DISTINCT device_id) as devices_with_params 
      FROM base_parameters
    `).get() as {total_params: number, devices_with_params: number} || {total_params: 0, devices_with_params: 0};

    return NextResponse.json({
      success: true,
      analysis: {
        totalDevices: allDevices.length,
        devicesWithTableIds: allDevices.length - unmappedDevices.length,
        unmappedDevices: unmappedDevices.length,
        availableExcelTables: availableTables.size,
        matchedTables: matchAnalysis.length,
        unmatchedTables: unmatchedTables.length,
        currentDatabaseParams: currentBaseParams
      },
      details: {
        availableTables: Array.from(availableTables.entries()).map(([id, info]) => ({
          tableId: id,
          description: info.description,
          parameterCount: info.parameterCount
        })),
        matchedTables: matchAnalysis,
        unmatchedTables: unmatchedTables,
        unmappedDevices: unmappedDevices
      }
    });

  } catch (error) {
    console.error('分析基础参数表错误:', error);
    return NextResponse.json({
      success: false,
      message: "分析过程中发生错误",
      error: String(error)
    });
  }
} 